home *** CD-ROM | disk | FTP | other *** search
Wrap
// DO NOT import this into the global namespace, but instead // import it into your own namespace wrapper var EXPORTED_SYMBOLS = ["LOGGING_DB"]; Components.utils.import("resource://glydo/utils/prototype_xul_1_6_0_3_modified.jsm"); Components.utils.import("resource://glydo/utils/io.jsm"); Components.utils.import("resource://glydo/utils/Prefs.jsm"); Components.utils.import("resource://glydo/utils/Utils.jsm"); Components.utils.import("resource://glydo/utils/PrivateBrowsing.jsm"); Components.utils.import("resource://glydo/ClientInfo.jsm"); LoggingDB = Prototype.Class.create({ initialize: function() { this.setupSchemaVersionsInfo(); this.available = false; this.privateBrowsing = new PrivateBrowsingListener(); this.privateBrowsing.addListener(this); this.setupDatabase(); }, setupDatabase: function() { this.schemaVersion = null; this.creatorClient = null; this.instanceId = null; this.storageService = Components.classes["@mozilla.org/storage/service;1"] .getService(Components.interfaces.mozIStorageService); var file = DirIO.get("ProfD"); file.append("glydo"); file.append("storage"); DirIO.create(file); this.usingPrivateDatabase = this.privateBrowsing.inPrivateBrowsing; file.append(this.usingPrivateDatabase ? "private.logging.sqlite" : "logging.sqlite"); this.databaseFile = file; this.updateSchema(); }, cleanPrivateDatabase: function() { if (this.usingPrivateDatabase && this.databaseFile) { this.databaseFile.remove(false); this.databaseFile = null; this.schemaVersion = null; this.creatorClient = null; this.instanceId = null; } }, onEnterPrivateBrowsing: function() { // We setup the database again, setting up the private database this.setupDatabase(); }, onExitPrivateBrowsing: function() { // We clean the private database this.cleanPrivateDatabase(); // We setup the new database this.setupDatabase(); }, setupSchemaVersionsInfo: function() { this.schemaVersionsInfo = [ { version: "0.1", upgrade: this.upgradeToV0_1 }, { version: "0.2", upgrade: this.upgradeToV0_2 }, { version: "0.3", upgrade: this.upgradeToV0_3 }, { version: "0.4", upgrade: this.upgradeToV0_4 } ]; }, connect: function(force) { if (!force && !this.available) { return null; } var conn = this.storageService.openDatabase(this.databaseFile); if (!conn.connectionReady) { return null; } return conn; }, updateSchema: function() { var conn = null; var ourTransaction = false; try { conn = this.connect(true); if (!conn) { return; } conn.beginTransaction(); var baseSchema = true; ourTransaction = true; this.getSchemaInfo(conn); // We first find the next schema version in the schema versions table var nSchemaVersions = this.schemaVersionsInfo.length; var nextSchemaVersionIndex = 0; if (this.schemaVersion !== null) { baseSchema = false; for (var i = 0; i < nSchemaVersions; ++i) { var schemaVersionInfo = this.schemaVersionsInfo[i]; if (schemaVersionInfo.version == this.schemaVersion) { nextSchemaVersionIndex = i+1; baseSchema = true; break; } } } // If the current schema version is unrecognized we abort, // leaving the DB in unavailable state if (!baseSchema) { return; } // Otherwise we upgrade the schema by steps for (var j = nextSchemaVersionIndex; j < nSchemaVersions; ++j) { var schemaVersionInfo = this.schemaVersionsInfo[j]; this.upgradeToSchemaVersion(conn,schemaVersionInfo); } // Update info from schema again this.getSchemaInfo(conn); conn.commitTransaction(); //FIXME: This won't really work well in high concurrency.. ourTransaction = false; this.available = true; } catch (e) { if (window.Components) { Components.utils.reportError(e); } } finally { if (conn) { if (ourTransaction && conn.transactionInProgress) { conn.rollbackTransaction(); } try { conn.close(); } catch (ex) { } conn = null; } } }, upgradeToSchemaVersion: function(conn,schemaVersionInfo) { var creatorClient = CLIENT_INFO.clientId + ":" + CLIENT_INFO.version; var instanceId = this.instanceId; if (instanceId === null) { instanceId = Utils.uuid1(); } schemaVersionInfo.upgrade.call(this,conn,schemaVersionInfo.version); var stmt = null; try { stmt = conn.createStatement("INSERT INTO schema_version (version,created_by_client,instance_id) VALUES (?1,?2,?3)"); stmt.bindUTF8StringParameter(0,schemaVersionInfo.version); stmt.bindUTF8StringParameter(1,creatorClient); stmt.bindUTF8StringParameter(2,instanceId); stmt.execute(); } finally { if (stmt) { try { try { stmt.finalize(); } catch (ex) { } } catch (ex) { } stmt = null; } } }, createBaseReportRow: function(conn,time) { var stmt = null; try { stmt = conn.createStatement("REPLACE INTO last_report (id,report_time) VALUES (1,?1)"); stmt.bindUTF8StringParameter(0,time.getTime()); stmt.execute(); } finally { if (stmt) { try { stmt.finalize(); } catch (ex) { } stmt = null; } } }, getSchemaInfo: function(conn) { this.schemaVersion = null; this.creatorClient = null; this.instanceId = null; var stmt = null; try { stmt = conn.createStatement("SELECT serial,version,created_by_client,instance_id FROM schema_version ORDER BY serial DESC LIMIT 1"); if (stmt.executeStep()) { this.schemaVersion = stmt.getUTF8String(1); this.creatorClient = stmt.getUTF8String(2); this.instanceId = stmt.getUTF8String(3); } } catch (e) { this.schemaVersion = null; this.creatorClient = null; this.instanceId = null; } finally { if (stmt) { stmt.reset(); try { stmt.finalize(); } catch (ex) { } stmt = null; } } }, logRecsRequest: function( req_time, resp_time, req_id, cancelled) { if (!this.available) { return; } var conn = null; var stmt = null; try { conn = this.connect(); if (!conn) { return; } stmt = conn.createStatement("INSERT INTO rec_reqs (req_time,resp_time,req_id,cancelled,client_version) VALUES (?1,?2,?3,?4,?5)"); var reqt = req_time ? Utils.toISO8601DateString(req_time) : null; var rest = resp_time ? Utils.toISO8601DateString(resp_time) : null; stmt.bindUTF8StringParameter(0,reqt); stmt.bindUTF8StringParameter(1,rest); stmt.bindUTF8StringParameter(2,req_id); stmt.bindInt32Parameter(3,cancelled ? 1 : 0); stmt.bindUTF8StringParameter(4,CLIENT_INFO.version); stmt.execute(); } catch (ex) { if (window.Components) { Components.utils.reportError(ex); } } finally { if (stmt) { try { stmt.finalize(); } catch (ex) { } stmt = null; } if (conn) { try { conn.close(); } catch (ex) { } conn = null; } } }, logCachedRecsReqHit: function( hit_time, req_id) { if (!this.available) { return; } var conn = null; var stmt = null; try { conn = this.connect(); if (!conn) { return; } stmt = conn.createStatement("INSERT INTO cached_rec_req_hits (hit_time,req_id,client_version) VALUES (?1,?2,?3)"); var hitt = Utils.toISO8601DateString(hit_time); stmt.bindUTF8StringParameter(0,hitt); stmt.bindUTF8StringParameter(1,req_id); stmt.bindUTF8StringParameter(2,CLIENT_INFO.version); stmt.execute(); } catch (ex) { if (window.Components) { Components.utils.reportError(ex); } } finally { if (stmt) { try { stmt.finalize(); } catch (ex) { } stmt = null; } if (conn) { try { conn.close(); } catch (ex) { } conn = null; } } }, logRecAction: function( req_id, rec_id, action, destination, real_estate_kind, real_estate_position) { if (!this.available) { return; } var conn = null; var stmt = null; try { conn = this.connect(); if (!conn) { return; } stmt = conn.createStatement("INSERT INTO recs_actions (event_time,req_id,rec_id,client_version,action,destination,real_estate_kind,real_estate_position) VALUES (?1,?2,?3,?4,?5,?6,?7,?8)"); var evt = Utils.toISO8601DateString(new Date()); stmt.bindUTF8StringParameter(0,evt); stmt.bindUTF8StringParameter(1,req_id); stmt.bindUTF8StringParameter(2,rec_id); stmt.bindUTF8StringParameter(3,CLIENT_INFO.version); stmt.bindUTF8StringParameter(4,action); stmt.bindUTF8StringParameter(5,destination); stmt.bindUTF8StringParameter(6,real_estate_kind); stmt.bindUTF8StringParameter(7,real_estate_position); stmt.execute(); } catch (ex) { if (window.Components) { Components.utils.reportError(ex); } } finally { if (stmt) { try { stmt.finalize(); } catch (ex) { } stmt = null; } if (conn) { try { conn.close(); } catch (ex) { } conn = null; } } }, logUserEvent: function(element,event,segments,value) { if (!this.available) { return; } var conn = null; var stmt = null; try { conn = this.connect(); if (!conn) { return; } stmt = conn.createStatement("INSERT INTO user_events (event_time,element,event,client_version,segments,value) VALUES (?1,?2,?3,?4,?5,?6)"); var evt = Utils.toISO8601DateString(new Date()); stmt.bindUTF8StringParameter(0,evt); stmt.bindUTF8StringParameter(1,element); stmt.bindUTF8StringParameter(2,event); stmt.bindUTF8StringParameter(3,CLIENT_INFO.version); if (segments) { stmt.bindUTF8StringParameter(4,Prototype.O.toJSON(segments)); } stmt.bindUTF8StringParameter(5,value); stmt.execute(); } catch (ex) { if (window.Components) { Components.utils.reportError(ex); } } finally { if (stmt) { try { stmt.finalize(); } catch (ex) { } stmt = null; } if (conn) { try { conn.close(); } catch (ex) { } conn = null; } } }, prepareReportIfNecessary: function(report_send_interval_ms) { if (!this.available) { return false; } if (this.privateBrowsing.inPrivateBrowsing) { // Disable reports in private browsing return false; } var conn = null; var stmt = null; try { conn = this.connect(); if (!conn) { return; } // Check time of latest report var latest_report_time = null; var last_rec_req_id = null; var last_rec_action_id = null; var last_cached_rec_req_hit_id = null; var last_user_event_id = null; try { // FIXME: Should we prevent multiple reporters from doing this in parallel, // in a stronger manner? stmt = conn.createStatement("SELECT report_time,last_rec_req_id,last_rec_action_id,last_cached_rec_req_hit_id,last_user_event_id FROM last_report"); if (stmt.executeStep()) { latest_report_time = stmt.getInt64(0); if (stmt.getTypeOfIndex(1) !== stmt.VALUE_TYPE_NULL) { last_rec_req_id = stmt.getInt64(1); } if (stmt.getTypeOfIndex(2) !== stmt.VALUE_TYPE_NULL) { last_rec_action_id = stmt.getInt64(2); } if (stmt.getTypeOfIndex(3) !== stmt.VALUE_TYPE_NULL) { last_cached_rec_req_hit_id = stmt.getInt64(3); } if (stmt.getTypeOfIndex(4) !== stmt.VALUE_TYPE_NULL) { last_user_event_id = stmt.getInt64(4); } } } finally { if (stmt) { stmt.reset(); try { stmt.finalize(); } catch (ex) { } stmt = null; } } var curTime = new Date(); // // if (latest_report_time === null) { this.createBaseReportRow(conn,curTime); return null; } // if (report_send_interval_ms && ((curTime.getTime() - latest_report_time) < report_send_interval_ms)) { return null; } // return this.prepareReport(conn,curTime,latest_report_time,last_rec_req_id,last_rec_action_id,last_cached_rec_req_hit_id,last_user_event_id); } catch (ex) { if (window.Components) { Components.utils.reportError(ex); } return null; } finally { if (conn) { try { conn.close(); } catch (ex) { } conn = null; } } }, prepareReport: function(conn,toTime,last_report_time,last_rec_req_id,last_rec_action_id,last_cached_rec_req_hit_id,last_user_event_id) { var reportData = { to_time: toTime.getTime(), last_rec_req_id: last_rec_req_id, last_rec_action_id: last_rec_action_id, last_cached_rec_req_hit_id: last_cached_rec_req_hit_id, last_user_event_id: last_user_event_id, report: { ClientID: CLIENT_INFO.clientId, AppID: CLIENT_INFO.appId, ClientSchemaCreator: this.creatorClient, ClientSchemaInstanceID: this.instanceId, From: Utils.toISO8601DateString(new Date(last_report_time)), To: Utils.toISO8601DateString(toTime), Events: { RecommendationRequests: [], RecommendationActions: [], CachedRecommendationRequestHits: [], UserEvents: [], }, Metrics: { } } }; var stmt = null; try { if (last_rec_req_id !== null) { stmt = conn.createStatement("SELECT id,req_time,resp_time,req_id,client_version,cancelled FROM rec_reqs WHERE id > ?1"); stmt.bindInt64Parameter(0,last_rec_req_id); } else { stmt = conn.createStatement("SELECT id,req_time,resp_time,req_id,client_version,cancelled FROM rec_reqs"); } while (stmt.executeStep()) { var rec_req = { id: stmt.getInt64(0), req_time: stmt.getUTF8String(1), resp_time: stmt.getUTF8String(2), req_id: stmt.getUTF8String(3), client_version: stmt.getUTF8String(4), cancelled: (stmt.getInt32(5) !== 0) }; reportData.report.Events.RecommendationRequests.push(rec_req); if ((reportData.last_rec_req_id === null) || (reportData.last_rec_req_id < rec_req.id)) { reportData.last_rec_req_id = rec_req.id; } } } finally { if (stmt) { try { stmt.finalize(); } catch (ex) { } stmt = null; } } try { if (last_rec_action_id !== null) { stmt = conn.createStatement("SELECT id,event_time,req_id,rec_id,client_version,action,destination,real_estate_kind,real_estate_position FROM recs_actions WHERE id > ?1"); stmt.bindInt64Parameter(0,last_rec_action_id); } else { stmt = conn.createStatement("SELECT id,event_time,req_id,rec_id,client_version,action,destination,real_estate_kind,real_estate_position FROM recs_actions"); } while (stmt.executeStep()) { var rec_action = { id: stmt.getInt64(0), event_time: stmt.getUTF8String(1), req_id: stmt.getUTF8String(2), rec_id: stmt.getUTF8String(3), client_version: stmt.getUTF8String(4), action: stmt.getUTF8String(5), destination: stmt.getUTF8String(6), real_estate_kind: stmt.getUTF8String(7), real_estate_position: stmt.getUTF8String(8) }; reportData.report.Events.RecommendationActions.push(rec_action); if ((reportData.last_rec_action_id === null) || (reportData.last_rec_action_id < rec_action.id)) { reportData.last_rec_action_id = rec_action.id; } } } finally { if (stmt) { try { stmt.finalize(); } catch (ex) { } stmt = null; } } var stmt = null; try { if (last_cached_rec_req_hit_id !== null) { stmt = conn.createStatement("SELECT id,hit_time,req_id,client_version FROM cached_rec_req_hits WHERE id > ?1"); stmt.bindInt64Parameter(0,last_cached_rec_req_hit_id); } else { stmt = conn.createStatement("SELECT id,hit_time,req_id,client_version FROM cached_rec_req_hits"); } while (stmt.executeStep()) { var cached_rec_req_hit = { id: stmt.getInt64(0), hit_time: stmt.getUTF8String(1), req_id: stmt.getUTF8String(2), client_version: stmt.getUTF8String(3) }; reportData.report.Events.CachedRecommendationRequestHits.push(cached_rec_req_hit); if ((reportData.last_cached_rec_req_hit_id === null) || (reportData.last_cached_rec_req_hit_id < cached_rec_req_hit.id)) { reportData.last_cached_rec_req_hit_id = cached_rec_req_hit.id; } } } finally { if (stmt) { try { stmt.finalize(); } catch (ex) { } stmt = null; } } var stmt = null; try { if (last_user_event_id !== null) { stmt = conn.createStatement("SELECT id,event_time,element,event,client_version,segments,value FROM user_events WHERE id > ?1"); stmt.bindInt64Parameter(0,last_user_event_id); } else { stmt = conn.createStatement("SELECT id,event_time,element,event,client_version,segments,value FROM user_events"); } while (stmt.executeStep()) { var user_event = { id: stmt.getInt64(0), event_time: stmt.getUTF8String(1), element: stmt.getUTF8String(2), event: stmt.getUTF8String(3), client_version: stmt.getUTF8String(4), segments: stmt.getUTF8String(5), value: stmt.getUTF8String(6), }; if (user_event.segments) { user_event.segments = Prototype.S.decodeJSON(user_event.segments); } reportData.report.Events.UserEvents.push(user_event); if ((reportData.last_user_event_id === null) || (reportData.last_user_event_id < user_event.id)) { reportData.last_user_event_id = user_event.id; } } } finally { if (stmt) { try { stmt.finalize(); } catch (ex) { } stmt = null; } } return reportData; }, markReportAsSent: function(reportData) { if (!this.available) { return false; } var conn = null; var stmt = null; try { conn = this.connect(); if (!conn) { return; } // Insert the record into the database try { stmt = conn.createStatement("UPDATE last_report SET report_time=?1, last_rec_req_id=?2, last_rec_action_id=?3, last_cached_rec_req_hit_id=?4, last_user_event_id=?5 WHERE id=1"); stmt.bindInt64Parameter(0,reportData.to_time); if (reportData.last_rec_req_id === null) { stmt.bindNullParameter(1); } else { stmt.bindInt64Parameter(1,reportData.last_rec_req_id); } if (reportData.last_rec_action_id === null) { stmt.bindNullParameter(2); } else { stmt.bindInt64Parameter(2,reportData.last_rec_action_id); } if (reportData.last_cached_rec_req_hit_id === null) { stmt.bindNullParameter(3); } else { stmt.bindInt64Parameter(3,reportData.last_cached_rec_req_hit_id); } if (reportData.last_user_event_id === null) { stmt.bindNullParameter(4); } else { stmt.bindInt64Parameter(4,reportData.last_user_event_id); } stmt.execute(); } finally { if (stmt) { try { stmt.finalize(); } catch (ex) { } stmt = null; } } // Try cleaning up unnecessary rows if (reportData.last_rec_req_id !== null) { try { stmt = conn.createStatement("DELETE FROM rec_reqs WHERE id <= ?1"); stmt.bindInt64Parameter(0,reportData.last_rec_req_id); stmt.execute(); } finally { if (stmt) { try { stmt.finalize(); } catch (ex) { } stmt = null; } } } if (reportData.last_rec_action_id !== null) { try { stmt = conn.createStatement("DELETE FROM recs_actions WHERE id <= ?1"); stmt.bindInt64Parameter(0,reportData.last_rec_action_id); stmt.execute(); } finally { if (stmt) { try { stmt.finalize(); } catch (ex) { } stmt = null; } } } if (reportData.last_cached_rec_req_hit_id !== null) { try { stmt = conn.createStatement("DELETE FROM cached_rec_req_hits WHERE id <= ?1"); stmt.bindInt64Parameter(0,reportData.last_cached_rec_req_hit_id); stmt.execute(); } finally { if (stmt) { try { stmt.finalize(); } catch (ex) { } stmt = null; } } } if (reportData.last_user_event_id !== null) { try { stmt = conn.createStatement("DELETE FROM user_events WHERE id <= ?1"); stmt.bindInt64Parameter(0,reportData.last_user_event_id); stmt.execute(); } finally { if (stmt) { try { stmt.finalize(); } catch (ex) { } stmt = null; } } } } catch (ex) { if (window.Components) { Components.utils.reportError(ex); } return null; } finally { if (stmt) { try { stmt.finalize(); } catch (ex) { } stmt = null; } if (conn) { try { conn.close(); } catch (ex) { } conn = null; } } }, getRecLastViewedInTeaserTime: function(url) { if (!this.available) { return null; } var conn = null; var stmt = null; try { conn = this.connect(); if (!conn) { return; } stmt = conn.createStatement("SELECT last_viewed_in_teaser_time FROM recs_acks WHERE url = ?1"); stmt.bindUTF8StringParameter(0,url); while (stmt.executeStep()) { return new Date(stmt.getInt64(0)); } return null; } catch (ex) { if (window.Components) { Components.utils.reportError(ex); } } finally { if (stmt) { try { stmt.finalize(); } catch (ex) { } stmt = null; } if (conn) { try { conn.close(); } catch (ex) { } conn = null; } } }, markAckedRecAsReceived: function(url,received_time) { if (!this.available) { return; } var conn = null; var stmt = null; try { conn = this.connect(); if (!conn) { return; } stmt = conn.createStatement("UPDATE recs_acks SET last_rec_received_time = ?1 WHERE url = ?2"); stmt.bindInt64Parameter(0,received_time.getTime()); stmt.bindUTF8StringParameter(1,url); stmt.execute(); } catch (ex) { if (window.Components) { Components.utils.reportError(ex); } } finally { if (stmt) { try { stmt.finalize(); } catch (ex) { } stmt = null; } if (conn) { try { conn.close(); } catch (ex) { } conn = null; } } }, markRecAsViewedInTeaser: function(url,received_time) { if (!this.available) { return; } var conn = null; var stmt = null; try { conn = this.connect(); if (!conn) { return; } var now = new Date().getTime(); try { stmt = conn.createStatement("INSERT OR REPLACE INTO recs_acks (url,last_viewed_in_teaser_time,last_rec_received_time) VALUES (?1,?2,?3)"); stmt.bindUTF8StringParameter(0,url); stmt.bindInt64Parameter(1,now); stmt.bindInt64Parameter(2,received_time.getTime()); stmt.execute(); } finally { if (stmt) { try { stmt.finalize(); } catch (ex) { } stmt = null; } } try { var cleanupMarkerTime = now - Prefs.db_recs_acks_max_age_millis; stmt = conn.createStatement("DELETE FROM recs_acks WHERE last_viewed_in_teaser_time < ?1 AND last_rec_received_time < ?2"); stmt.bindInt64Parameter(0,cleanupMarkerTime); stmt.bindInt64Parameter(1,cleanupMarkerTime); stmt.execute(); } finally { if (stmt) { try { stmt.finalize(); } catch (ex) { } stmt = null; } } } catch (ex) { if (window.Components) { Components.utils.reportError(ex); } } finally { if (conn) { try { conn.close(); } catch (ex) { } conn = null; } } }, upgradeToV0_1: function(conn,version) { if (version != "0.1") { throw "Unsupported version number for upgrade: " + version; } conn.executeSimpleSQL("CREATE TABLE IF NOT EXISTS schema_version (serial INTEGER PRIMARY KEY ASC AUTOINCREMENT, version VARCHAR(30), created_by_client VARCHAR(255), instance_id VARCHAR(36))"); conn.executeSimpleSQL("CREATE TABLE IF NOT EXISTS rec_reqs (id INTEGER PRIMARY KEY ASC AUTOINCREMENT, req_time VARCHAR(23), resp_time VARCHAR(23), req_id VARCHAR(255), client_version VARCHAR(255), cancelled INTEGER NOT NULL)"); conn.executeSimpleSQL("CREATE TABLE IF NOT EXISTS recs_followed (id INTEGER PRIMARY KEY ASC AUTOINCREMENT, event_time VARCHAR(23), req_id VARCHAR(255) NOT NULL, rec_id VARCHAR(255) NOT NULL, client_version VARCHAR(255))"); conn.executeSimpleSQL("CREATE TABLE IF NOT EXISTS reports (id INTEGER PRIMARY KEY, report_time INTEGER NOT NULL, last_rec_req_id INTEGER NULL, last_rec_followed_id INTEGER NULL)"); }, upgradeToV0_2: function(conn,version) { if (version != "0.2") { throw "Unsupported version number for upgrade: " + version; } conn.executeSimpleSQL("CREATE TABLE IF NOT EXISTS recs_acks (url VARCHAR(2047) PRIMARY KEY, last_viewed_in_teaser_time INTEGER, last_rec_received_time INTEGER NOT NULL)"); conn.executeSimpleSQL("CREATE INDEX IF NOT EXISTS recs_acks_last_viewed_in_teaser_time_idx ON recs_acks (last_viewed_in_teaser_time ASC)"); conn.executeSimpleSQL("CREATE INDEX IF NOT EXISTS recs_acks_last_rec_received_time_idx ON recs_acks (last_rec_received_time ASC)"); }, upgradeToV0_3: function(conn,version) { if (version != "0.3") { throw "Unsupported version number for upgrade: " + version; } conn.executeSimpleSQL("CREATE TABLE IF NOT EXISTS cached_rec_req_hits (id INTEGER PRIMARY KEY ASC AUTOINCREMENT, hit_time VARCHAR(23) NOT NULL, req_id VARCHAR(255), client_version VARCHAR(255))"); conn.executeSimpleSQL("ALTER TABLE reports ADD COLUMN last_cached_rec_req_hit_id INTEGER NULL"); }, upgradeToV0_4: function(conn,version) { if (version != "0.4") { throw "Unsupported version number for upgrade: " + version; } // Add recommendation actions and real estate information conn.executeSimpleSQL("ALTER TABLE recs_followed RENAME TO recs_actions"); // action is one of OPEN or SHARE for now conn.executeSimpleSQL("ALTER TABLE recs_actions ADD COLUMN action VARCHAR(40) NOT NULL DEFAULT 'OPEN'"); // destination is: // * CURRENT_TAB, NEW_TAB, NEW_WINDOW, FOCUS_EXISTING, or MAIL for the OPEN action // * one the sharing destinations for the SHARE action conn.executeSimpleSQL("ALTER TABLE recs_actions ADD COLUMN destination VARCHAR(40) NULL DEFAULT NULL"); // real_estate_kind is one of STATUSBAR_TICKER, TEASER, or STATUSBAR_POPUP conn.executeSimpleSQL("ALTER TABLE recs_actions ADD COLUMN real_estate_kind VARCHAR(40) NULL DEFAULT NULL"); // real_estate_position is a description, specific to the real_estate_kind, of exactly where the recommendation was located inside the real estate conn.executeSimpleSQL("ALTER TABLE recs_actions ADD COLUMN real_estate_position VARCHAR(255) NULL DEFAULT NULL"); // Add other user events table conn.executeSimpleSQL("CREATE TABLE IF NOT EXISTS user_events (id INTEGER PRIMARY KEY ASC AUTOINCREMENT, event_time VARCHAR(23), element VARCHAR(255) NOT NULL, event VARCHAR(255) NOT NULL, client_version VARCHAR(255), segments VARCHAR(255) DEFAULT NULL, value VARCHAR(255) DEFAULT NULL)"); // We create a new last_report table to replace the reports table. We also add a new column and rename the deprecated last_rec_followed_id conn.executeSimpleSQL("ALTER TABLE reports RENAME TO reports_old"); conn.executeSimpleSQL("CREATE TABLE IF NOT EXISTS last_report (id INTEGER PRIMARY KEY, report_time INTEGER NOT NULL, last_rec_req_id INTEGER NULL, last_rec_action_id INTEGER NULL, last_cached_rec_req_hit_id INTEGER NULL, last_user_event_id INTEGER NULL)"); conn.executeSimpleSQL("INSERT INTO last_report (id,report_time, last_rec_req_id, last_rec_action_id, last_cached_rec_req_hit_id) SELECT 1,report_time,last_rec_req_id,last_rec_followed_id,last_cached_rec_req_hit_id FROM reports_old ORDER BY id DESC LIMIT 1"); conn.executeSimpleSQL("DROP TABLE reports_old"); } }); var LOGGING_DB = new LoggingDB();